﻿using System;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using Shuba.Model;

namespace Shuba.Data
{
    public class UserDevice
    {        
        public List<UserDeviceInfo> GetList(string keyword)
        {
            List<UserDeviceInfo> result = new List<UserDeviceInfo>();
            Database mydb = DBConnection.CreateDatabase();

            string search = string.Format("%{0}%", keyword).Replace("'", "''");

            string mysql = string.Format(@"SELECT NIP, IMEI, TOKEN, EXPIRED, CURRENT_VERSION, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE
                                           FROM TBL_MS_USER_DEVICE WHERE NIP LIKE '{0}' OR IMEI LIKE '{0}'", search);

            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);

            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    result.Add(Create(mydr));
                }
                mydr.Close();
            }
            return result;
        }        

        private UserDeviceInfo Create(IDataReader dr)
        {
            UserDeviceInfo info = new UserDeviceInfo();
            info.NIP = dr.GetString(dr.GetOrdinal("NIP"));
            info.IMEI = dr.GetString(dr.GetOrdinal("IMEI"));
            if (!dr.IsDBNull(dr.GetOrdinal("TOKEN")))
                info.Token = dr.GetString(dr.GetOrdinal("TOKEN"));
            if (!dr.IsDBNull(dr.GetOrdinal("EXPIRED")))
                info.Expired = dr.GetDateTime(dr.GetOrdinal("EXPIRED"));
            if (!dr.IsDBNull(dr.GetOrdinal("CURRENT_VERSION")))
                info.CurrentVersion = dr.GetString(dr.GetOrdinal("CURRENT_VERSION"));
            info.EntryBy = dr.GetString(dr.GetOrdinal("ENTRY_BY"));
            info.EntryDate = dr.GetDateTime(dr.GetOrdinal("ENTRY_DATE"));
            info.UpdateBy = dr.GetString(dr.GetOrdinal("UPDATE_BY"));
            info.UpdateDate = dr.GetDateTime(dr.GetOrdinal("UPDATE_DATE"));

            return info;

        }

        public UserDeviceInfo Get(string NIP)
        {
            UserDeviceInfo info = null;
            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"SELECT NIP, IMEI, TOKEN, EXPIRED, CURRENT_VERSION, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE
                                           FROM TBL_MS_USER_DEVICE WHERE NIP = @NIP";

            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);
            mydb.AddInParameter(mycomm, "@NIP", DbType.String, NIP);


            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    info = Create(mydr);
                }
                mydr.Close();
            }
            return info;
        }

        public UserDeviceInfo GetByIMEI(string IMEI)
        {
            UserDeviceInfo info = null;
            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"SELECT NIP, IMEI, TOKEN, EXPIRED, CURRENT_VERSION, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE
                                           FROM TBL_MS_USER_DEVICE WHERE IMEI = @IMEI";

            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);
            mydb.AddInParameter(mycomm, "@IMEI", DbType.String, IMEI);

            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    info = Create(mydr);
                }
                mydr.Close();
            }
            return info;
        }

        

        public int Save(UserDeviceInfo info)
        {         
            Database mydb = DBConnection.CreateDatabase();

            DbCommand mycomm = mydb.GetSqlStringCommand(@"
                       	    UPDATE TBL_MS_USER_DEVICE_TMP SET 
                                IMEI = @IMEI,                                
                                ENTRY_BY = @ENTRY_BY,
                                ENTRY_DATE = @ENTRY_DATE,
                                UPDATE_BY = @UPDATE_BY,
                                UPDATE_DATE = @UPDATE_DATE,
                                ACT_TYPE = @ACT_TYPE                                                        
                                WHERE NIP = @NIP;
            	            IF @@ROWCOUNT = 0
                            INSERT INTO TBL_MS_USER_DEVICE_TMP(NIP,IMEI,ENTRY_BY,ENTRY_DATE,UPDATE_BY,UPDATE_DATE,ACT_TYPE) 
                            VALUES (@NIP,@IMEI,@ENTRY_BY,@ENTRY_DATE,@UPDATE_BY,@UPDATE_DATE,@ACT_TYPE)");

            mydb.AddInParameter(mycomm, "@NIP", DbType.String, info.NIP);
            mydb.AddInParameter(mycomm, "@IMEI", DbType.String, info.IMEI);
            mydb.AddInParameter(mycomm, "@ENTRY_BY", DbType.String, info.EntryBy);
            mydb.AddInParameter(mycomm, "@ENTRY_DATE", DbType.DateTime, info.EntryDate);
            mydb.AddInParameter(mycomm, "@UPDATE_BY", DbType.String, info.UpdateBy);
            mydb.AddInParameter(mycomm, "@UPDATE_DATE", DbType.DateTime, info.UpdateDate);
            mydb.AddInParameter(mycomm, "@ACT_TYPE", DbType.String, info.ActType);

            return mydb.ExecuteNonQuery(mycomm);
        }

        public List<UserDeviceInfo> GetListApproval(string UPDATE_BY)
        {
            List<UserDeviceInfo> result = new List<UserDeviceInfo>();
            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"SELECT NIP, IMEI, TOKEN, EXPIRED, CURRENT_VERSION, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE, ACT_TYPE
                                           FROM TBL_MS_USER_DEVICE_TMP WHERE UPDATE_BY <> @UPDATE_BY";
            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);
            mydb.AddInParameter(mycomm, "@UPDATE_BY", DbType.String, UPDATE_BY);

            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    UserDeviceInfo info = Create(mydr);
                    info.ActType = mydr.GetString(mydr.GetOrdinal("ACT_TYPE"));
                    result.Add(info);
                }
                mydr.Close();
            }
            return result;
        }        

        public void Approve(string NIP, string ACT_TYPE)
        {
            Database mydb = DBConnection.CreateDatabase();
            DbCommand mycomm = mydb.GetStoredProcCommand("APPROVAL_USER_DEVICE");
            mydb.AddInParameter(mycomm, "@NIP", DbType.String, NIP);
            mydb.AddInParameter(mycomm, "@ACT_TYPE", DbType.String, ACT_TYPE);

            mydb.ExecuteNonQuery(mycomm);
        }

        public void Reject(string nip)
        {
            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"DELETE FROM TBL_MS_USER_DEVICE_TMP
                             WHERE NIP = @NIP";
            DbCommand mycmd = mydb.GetSqlStringCommand(mysql);
            mydb.AddInParameter(mycmd, "@NIP", DbType.String, nip);

            mydb.ExecuteNonQuery(mycmd);
        }

        public UserDeviceInfo GetApprovalByID(string nip)
        {
            UserDeviceInfo info = new UserDeviceInfo();
            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"SELECT NIP, IMEI, TOKEN, EXPIRED, CURRENT_VERSION, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE, ACT_TYPE
                                           FROM TBL_MS_USER_DEVICE_TMP WHERE NIP = @NIP";
            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);
            mydb.AddInParameter(mycomm, "@NIP", DbType.String, nip);

            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    info = Create(mydr);
                    info.ActType = mydr.GetString(mydr.GetOrdinal("ACT_TYPE"));
                }
                mydr.Close();
            }
            return info;
        }
    }
}
